Skip Headers

Oracle® Database Globalization Support Guide 10g Release 2 (10.2) Part Number B14225-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 4 Datetime Datatypes and Time Zone Support

This chapter includes the following topics:

- Overview of Datetime and Interval Datatypes and Time Zone Support

- Datetime and Interval Datatypes

- Datetime and Interval Arithmetic and Comparisons

- Datetime SQL Functions

- Datetime and Time Zone Parameters and Environment Variables

- Choosing a Time Zone File

- Upgrading the Time Zone File

- Setting the Database Time Zone

- Converting Time Zones With the AT TIME ZONE Clause

- Setting the Session Time Zone

- Support for Daylight Saving Time

## Overview of Datetime and Interval Datatypes and Time Zone Support

Businesses conduct transactions across time zones. Oracle's datetime and interval datatypes and time zone support make it possible to store consistent information about the time of events and transactions.

Note: This chapter describes Oracle datetime and interval datatypes. It does not attempt to describe ANSI datatypes or other kinds of datatypes except when noted.

## Datetime and Interval Datatypes

The datetime datatypes are `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE,` and `TIMESTAMP WITH LOCAL TIME ZONE`. Values of datetime datatypes are sometimes called datetimes.

The interval datatypes are `INTERVAL YEAR TO MONTH` and `INTERVAL DAY TO SECOND`. Values of interval datatypes are sometimes called intervals.

Both datetimes and intervals are made up of fields. The values of these fields determine the value of the datatype. The fields that apply to all Oracle datetime and interval datatypes are:

- `YEAR`

- `MONTH`

- `DAY`

- `HOUR`

- `MINUTE`

- `SECOND`

`TIMESTAMP WITH TIME ZONE` also includes these fields:

- `TIMEZONE_HOUR`

- `TIMEZONE_MINUTE`

- `TIMEZONE_REGION`

- `TIMEZONE_ABBR`

`TIMESTAMP WITH LOCAL TIME ZONE` does not store time zone information internally, but you can see local time zone information in SQL output if the `TZH:TZM` or `TZR` `TZD` format elements are specified.

The following sections describe the datetime datatypes and interval datatypes in more detail:

- Datetime Datatypes

- Interval Datatypes

  See Also: Oracle Database SQL Reference for the valid values of the datetime and interval fields. Oracle Database SQL Reference also contains information about format elements.

### Datetime Datatypes

This section includes the following topics:

- DATE Datatype

- TIMESTAMP Datatype

- TIMESTAMP WITH TIME ZONE Datatype

- TIMESTAMP WITH LOCAL TIME ZONE Datatype

- Inserting Values into Datetime Datatypes

- Choosing a TIMESTAMP Datatype

#### DATE Datatype

The `DATE` datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the `DATE` datatype has special associated properties. For each `DATE` value, Oracle stores the following information: century, year, month, date, hour, minute, and second.

You can specify a date value by:

- Specifying the date value as a literal

- Converting a character or numeric value to a date value with the `TO_DATE` function

A date can be specified as an ANSI date literal or as an Oracle date value.

An ANSI date literal contains no time portion and must be specified in exactly the following format:

    DATE 'YYYY-MM-DD'

The following is an example of an ANSI date literal:

    DATE '1998-12-25'

Alternatively, you can specify an Oracle date value as shown in the following example:

    TO_DATE('1998-DEC-25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN')

The default date format for an Oracle date value is derived from the `NLS_DATE_FORMAT` and `NLS_DATE_LANGUAGE` initialization parameters. The date format in the example includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation. The specification for `NLS_DATE_LANGUAGE` is included because '`DEC'` is not a valid value for `MON` in all locales.

Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions.

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.

Oracle `DATE` columns always contain fields for both date and time. If your queries use a date format without a time portion, then you must ensure that the time fields in the `DATE` column are set to midnight. You can use the `TRUNC` (date) SQL function to ensure that the time fields are set to midnight, or you can make the query a test of greater than or less than (`<`, `<=`, `>=`, or `>)` instead of equality or inequality (`=` or `!=)`. Otherwise, Oracle may not return the query results you expect.

See Also: Oracle Database SQL Reference for more information about the `DATE` datatype "NLS_DATE_FORMAT" "NLS_DATE_LANGUAGE" Oracle Database SQL Reference for more information about literals, format elements such as `MM`, and the `TO_DATE` function

#### TIMESTAMP Datatype

The `TIMESTAMP` datatype is an extension of the `DATE` datatype. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the `DATE` datatype.

Specify the `TIMESTAMP` datatype as follows:

    TIMESTAMP [(fractional_seconds_precision)]

`fractional_seconds_precision` is optional and specifies the number of digits in the fractional part of the `SECOND` datetime field. It can be a number in the range 0 to 9. The default is 6.

For example, `'26-JUN-02 09:39:16.78'` shows 16.78 seconds. The fractional seconds precision is 2 because there are 2 digits in '`78`'.

You can specify the `TIMESTAMP` literal in a format like the following:

    TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

Using the example format, specify `TIMESTAMP` as a literal as follows:

    TIMESTAMP '1997-01-31 09:26:50.12'

The value of `NLS_TIMESTAMP_FORMAT` initialization parameter determines the timestamp format when a character string is converted to the `TIMESTAMP` datatype. `NLS_DATE_LANGUAGE` determines the language used for character data such as `MON`.

See Also: Oracle Database SQL Reference for more information about the `TIMESTAMP` datatype "NLS_TIMESTAMP_FORMAT" "NLS_DATE_LANGUAGE"

#### TIMESTAMP WITH TIME ZONE Datatype

`TIMESTAMP WITH TIME ZONE` is a variant of `TIMESTAMP` that includes a time zone offset or time zone region name in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time). Specify the `TIMESTAMP WITH TIME ZONE` datatype as follows:

    TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

`fractional_seconds_precision` is optional and specifies the number of digits in the fractional part of the `SECOND` datetime field.

You can specify `TIMESTAMP WITH TIME ZONE` as a literal as follows:

    TIMESTAMP '1997-01-31 09:26:56.66 +02:00'

Two `TIMESTAMP WITH TIME ZONE` values are considered identical if they represent the same instant in UTC, regardless of the `TIME ZONE` offsets stored in the data. For example, the following expressions have the same value:

    TIMESTAMP '1999-01-15 8:00:00 -8:00'
    TIMESTAMP '1999-01-15 11:00:00 -5:00'

You can replace the UTC offset with the `TZR` (time zone region) format element. The following expression specifies `US/Pacific` for the time zone region:

    TIMESTAMP '1999-01-15 8:00:00 US/Pacific'

To eliminate the ambiguity of boundary cases when the time switches from Standard Time to Daylight Saving Time, use both the `TZR` format element and the corresponding `TZD` format element. The `TZD` format element is an abbreviation of the time zone region with Daylight Saving Time information included. Examples are `PST` for US/Pacific standard time and `PDT` for US/Pacific daylight time. The following specification ensures that a Daylight Saving Time value is returned:

    TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'

If you do not add the `TZD` format element, and the datetime value is ambiguous, then Oracle returns an error if you have the `ERROR_ON_OVERLAP_TIME` session parameter set to `TRUE`. If `ERROR_ON_OVERLAP_TIME` is set to `FALSE` (the default value), then Oracle interprets the ambiguous datetime as Standard Time.

The default date format for the `TIMESTAMP WITH TIME ZONE` datatype is determined by the value of the `NLS_TIMESTAMP_TZ_FORMAT` initialization parameter.

See Also: Oracle Database SQL Reference for more information about the `TIMESTAMP WITH TIME ZONE` datatype "TIMESTAMP Datatype" for more information about fractional seconds precision "Support for Daylight Saving Time" "NLS_TIMESTAMP_TZ_FORMAT" Oracle Database SQL Reference for more information about format elements Oracle Database SQL Reference for more information about setting the `ERROR_ON_OVERLAP_TIME` session parameter

#### TIMESTAMP WITH LOCAL TIME ZONE Datatype

`TIMESTAMP WITH LOCAL TIME ZONE` is another variant of `TIMESTAMP`. It differs from `TIMESTAMP WITH TIME ZONE` as follows: data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time).

Specify the `TIMESTAMP WITH LOCAL TIME ZONE` datatype as follows:

    TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

`fractional_seconds_precision` is optional and specifies the number of digits in the fractional part of the `SECOND` datetime field.

There is no literal for `TIMESTAMP WITH LOCAL TIME ZONE`, but `TIMESTAMP` literals and `TIMESTAMP WITH TIME ZONE` literals can be inserted into a `TIMESTAMP WITH LOCAL TIME ZONE` column.

The default date format for `TIMESTAMP WITH LOCAL TIME ZONE` is determined by the value of the `NLS_TIMESTAMP_FORMAT` initialization parameter.

See Also: Oracle Database SQL Reference for more information about the `TIMESTAMP WITH LOCAL TIME ZONE` datatype "TIMESTAMP Datatype" for more information about fractional seconds precision "NLS_TIMESTAMP_FORMAT"

#### Inserting Values into Datetime Datatypes

You can insert values into a datetime column in the following ways:

- Insert a character string whose format is based on the appropriate NLS format value

- Insert a literal

- Insert a literal for which implicit conversion is performed

- Use the `TO_TIMESTAMP`, `TO_TIMESTAMP_TZ`, or `TO_DATE` SQL function

The following examples show how to insert data into datetime datatypes.

Example 4-1 Inserting Data into a DATE Column

Set the date format.

    SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Create a table `table_dt` with columns `c_id` and `c_dt`. The `c_id` column is of `NUMBER` datatype and helps to identify the method by which the data is entered. The `c_dt` column is of `DATE` datatype.

    SQL> CREATE TABLE table_dt (c_id NUMBER, c_dt DATE);

Insert a date as a character string.

    SQL> INSERT INTO table_dt VALUES(1, '01-JAN-2003');

Insert the same date as a `DATE` literal.

    SQL> INSERT INTO table_dt VALUES(2, DATE '2003-01-01');

Insert the date as a `TIMESTAMP` literal. Oracle drops the time zone information.

    SQL> INSERT INTO table_dt VALUES(3, TIMESTAMP '2003-01-01 00:00:00 US/Pacific');

Insert the date with the `TO_DATE` function.

    SQL> INSERT INTO table_dt VALUES(4, TO_DATE('01-JAN-2003', 'DD-MON-YYYY'));

Display the data.

    SQL> SELECT * FROM table_dt;

    C_ID       C_DT
    ---------- --------------------
    1          01-JAN-2003 00:00:00
    2          01-JAN-2003 00:00:00
    3          01-JAN-2003 00:00:00
    4          01-JAN-2003 00:00:00

Example 4-2 Inserting Data into a TIMESTAMP Column

Set the timestamp format.

    SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';

Create a table `table_ts` with columns `c_id` and `c_ts`. The `c_id` column is of `NUMBER` datatype and helps to identify the method by which the data is entered. The `c_ts` column is of `TIMESTAMP` datatype.

    SQL> CREATE TABLE table_ts(c_id NUMBER, c_ts TIMESTAMP);

Insert a date and time as a character string.

    SQL> INSERT INTO table_ts VALUES(1, '01-JAN-2003 2:00:00');

Insert the same date and time as a `TIMESTAMP` literal.

    SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

Insert the same date and time as a `TIMESTAMP WITH TIME ZONE` literal. Oracle converts it to a `TIMESTAMP` value, which means that the time zone information is dropped.

    SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

Display the data.

    SQL> SELECT * FROM table_ts;

    C_ID        C_TS
    ----------  -----------------------------
    1           01-JAN-03 02:00:00.000000 AM
    2           01-JAN-03 02:00:00.000000 AM
    3           01-JAN-03 02:00:00.000000 AM

Note that the three methods result in the same value being stored.

Example 4-3 Inserting Data into the TIMESTAMP WITH TIME ZONE Datatype

Set the timestamp format.

    SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';

Set the time zone to `'-07:00'`.

    SQL> ALTER SESSION SET TIME_ZONE='-7:00';

Create a table `table_tstz` with columns `c_id` and `c_tstz`. The `c_id` column is of `NUMBER` datatype and helps to identify the method by which the data is entered. The `c_tstz` column is of `TIMESTAMP WITH TIME ZONE` datatype.

    SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE);

Insert a date and time as a character string.

    SQL> INSERT INTO table_tstz VALUES(1, '01-JAN-2003 2:00:00 AM -07:00');

Insert the same date and time as a `TIMESTAMP` literal. Oracle converts it to a `TIMESTAMP WITH TIME ZONE` literal, which means that the session time zone is appended to the `TIMESTAMP` value.

    SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

Insert the same date and time as a `TIMESTAMP WITH TIME ZONE` literal.

    SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -8:00');

Display the data.

    SQL> SELECT * FROM table_tstz;

    C_ID        C_TSTZ
    ----------  ------------------------------------
    1           01-JAN-03 02:00.00:000000 AM -07:00
    2           01-JAN-03 02:00:00.000000 AM -07:00
    3           01-JAN-03 02:00:00.000000 AM -08:00

Note that the time zone is different for method 3, because the time zone information was specified as part of the `TIMESTAMP WITH TIME ZONE` literal.

Example 4-4 Inserting Data into the TIMESTAMP WITH LOCAL TIME ZONE Datatype

Consider data that is being entered in Denver, Colorado, U.S.A., whose time zone is UTC-7.

    SQL> ALTER SESSION SET TIME_ZONE='-07:00';

Create a table `table_tsltz` with columns `c_id` and `c_tsltz`. The `c_id` column is of `NUMBER` datatype and helps to identify the method by which the data is entered. The `c_tsltz` column is of `TIMESTAMP WITH LOCAL TIME ZONE` datatype.

    SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);

Insert a date and time as a character string.

    SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');

Insert the same data as a `TIMESTAMP WITH LOCAL TIME ZONE` literal.

    SQL> INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

Insert the same data as a `TIMESTAMP WITH TIME ZONE` literal. Oracle converts the data to a `TIMESTAMP WITH LOCAL TIME ZONE` value. This means the time zone that is entered (`-08:00`) is converted to the session time zone value (`-07:00`).

    SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

Display the data.

    SQL> SELECT * FROM table_tsltz;

    C_ID        C_TSLTZ
    ----------  ------------------------------------
    1           01-JAN-03 02.00.00.000000 AM
    2           01-JAN-03 02.00.00.000000 AM
    3           01-JAN-03 03.00.00.000000 AM

Note that the information that was entered as UTC-8 has been changed to the local time zone, changing the hour from `2` to `3`.

See Also: "Datetime SQL Functions" for more information about the `TO_TIMESTAMP` or `TO_TIMESTAMP_TZ` SQL functions

#### Choosing a TIMESTAMP Datatype

Use the `TIMESTAMP` datatype when you need a datetime value without locale information. For example, you can store information about the times when workers punch a timecard in and out of their assembly line workstations. The `TIMESTAMP` datatype uses 7 or 11 bytes of storage.

Use the `TIMESTAMP WITH TIME ZONE` datatype when the application is used across time zones. Consider a banking company with offices around the world. It records a deposit to an account at 11 a.m. in London and a withdrawal of the same amount from the account at 9 a.m. in New York. The money is in the account for three hours. Unless time zone information is stored with the account transactions, it appears that the account is overdrawn from 9 a.m. to 11 a.m.

The `TIMESTAMP WITH TIME ZONE` datatype requires 13 bytes of storage, or two more bytes of storage than the `TIMESTAMP` and `TIMESTAMP WITH LOCAL TIME ZONE` datatypes because it stores time zone information. The time zone is stored as an offset from UTC or as a time zone region name. The data is available for display or calculations without additional processing. A `TIMESTAMP WITH TIME ZONE` column cannot be used as a primary key. If an index is created on a `TIMESTAMP WITH TIME ZONE` column, it becomes a function-based index.

The `TIMESTAMP WITH LOCAL TIME ZONE` datatype stores the timestamp without time zone information. It normalizes the data to the database time zone every time the data is sent to and from a client. It requires 11 bytes of storage.

The `TIMESTAMP WITH LOCAL TIME ZONE` datatype is appropriate when the original time zone is of no interest, but the relative times of events are important. Consider the transactions described in the previous banking example. Suppose the data is recorded using the `TIMESTAMP WITH LOCAL TIME ZONE` datatype. If the database time zone of the bank is set to `Asia/Hong_Kong`, then an employee in Hong Kong who displays the data would see that the deposit was made at 7 p.m. and the withdrawal was made at 10 p.m. If the same data is displayed in London, it would show that the deposit was made at 11 a.m. and the withdrawal was made at 2 p.m. The three-hour difference is preserved, but the time zone/region of the original transaction is not. Because of this, the actual time of the transaction can be interpreted differently depending on the time zone/region from which the information is retrieved. For example, in London, the transactions appear to be conducted within business hours, in Hong Kong, they do not.

Note that, because the original time zone region of the time data is not preserved in the `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data type, time data referring to times from regions such as Brazil and Israel, regions that update their Daylight Savings Transition dates frequently and at irregular periods, may be inaccurate. If time information from these regions is key to your application, you may wish to consider using one of the other datetime types.

### Interval Datatypes

Interval datatypes store time durations. They are used primarily with analytic functions. For example, you can use them to calculate a moving average of stock prices. You must use interval datatypes to determine the values that correspond to a particular percentile. You can also use interval datatypes to update historical tables.

This section includes the following topics:

- INTERVAL YEAR TO MONTH Datatype

- INTERVAL DAY TO SECOND Datatype

- Inserting Values into Interval Datatypes

  See Also: Oracle Data Warehousing Guide for more information about analytic functions, including moving averages and inverse percentiles

#### INTERVAL YEAR TO MONTH Datatype

`INTERVAL YEAR TO MONTH` stores a period of time using the `YEAR` and `MONTH` datetime fields. Specify `INTERVAL YEAR TO MONTH` as follows:

    INTERVAL YEAR [(year_precision)] TO MONTH

`year_precision` is the number of digits in the `YEAR` datetime field. Accepted values are 0 to 9. The default value of `year_precision` is 2.

Interval values can be specified as literals. There are many ways to specify interval literals.The following is one example of specifying an interval of 123 years and 2 months.The year precision is 3.

    INTERVAL '123-2' YEAR(3) TO MONTH

See Also: Oracle Database SQL Reference for more information about specifying interval literals with the `INTERVAL YEAR TO MONTH` datatype

#### INTERVAL DAY TO SECOND Datatype

`INTERVAL DAY TO SECOND` stores a period of time in terms of days, hours, minutes, and seconds. Specify this datatype as follows:

    INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

`day_precision` is the number of digits in the `DAY` datetime field. Accepted values are 0 to 9. The default is 2.

`fractional_seconds_precision` is the number of digits in the fractional part of the `SECOND` datetime field. Accepted values are 0 to 9. The default is 6.

The following is one example of specifying an interval of 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second. The fractional second precision is 3.

    INTERVAL '4 5:12:10.222' DAY TO SECOND(3)

Interval values can be specified as literals. There are many ways to specify interval literals.

See Also: Oracle Database SQL Reference for more information about specifying interval literals with the `INTERVAL DAY TO SECOND` datatype

#### Inserting Values into Interval Datatypes

You can insert values into an interval column in the following ways:

- Insert an interval as a literal. For example:

      INSERT INTO table1 VALUES (INTERVAL '4-2' YEAR TO MONTH);

  This statement inserts an interval of 4 years and 2 months.

  Oracle recognizes literals for other ANSI interval types and converts the values to Oracle interval values.

- Use the `NUMTODSINTERVAL`, `NUMTOYMINTERVAL`, `TO_DSINTERVAL`, and `TO_YMINTERVAL` SQL functions.

  See Also: "Datetime SQL Functions"

## Datetime and Interval Arithmetic and Comparisons

This section includes the following topics:

- Datetime and Interval Arithmetic

- Datetime Comparisons

- Explicit Conversion of Datetime Datatypes

### Datetime and Interval Arithmetic

You can perform arithmetic operations on date (`DATE`), timestamp (`TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, and `TIMESTAMP WITH LOCAL TIME ZONE`) and interval (`INTERVAL DAY TO SECOND` and `INTERVAL YEAR TO MONTH`) data. You can maintain the most precision in arithmetic operations by using a timestamp datatype with an interval datatype.

You can use `NUMBER` constants in arithmetic operations on date and timestamp values. Oracle internally converts timestamp values to date values before doing arithmetic operations on them with `NUMBER` constants. This means that information about fractional seconds is lost during operations that include both date and timestamp values. Oracle interprets `NUMBER` constants in datetime and interval expressions as number of days.

Each `DATE` value contains a time component. The result of many date operations includes a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours. These fractions are also returned by Oracle built-in SQL functions for common operations on `DATE` data. For example, the built-in `MONTHS_BETWEEN` SQL function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.

Oracle performs all timestamp arithmetic in UTC time. For `TIMESTAMP WITH LOCAL TIME ZONE` data, Oracle converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For `TIMESTAMP WITH TIME ZONE` data, the datetime value is always in UTC, so no conversion is necessary.

See Also: Oracle Database SQL Reference for detailed information about datetime and interval arithmetic operations "Datetime SQL Functions" for information about which functions cause implicit conversion to `DATE`

### Datetime Comparisons

When you compare date and timestamp values, Oracle converts the data to the more precise datatype before doing the comparison. For example, if you compare data of `TIMESTAMP WITH TIME ZONE` datatype with data of `TIMESTAMP` datatype, Oracle converts the `TIMESTAMP` data to `TIMESTAMP WITH TIME ZONE`, using the session time zone.

The order of precedence for converting date and timestamp data is as follows:

1. `DATE`

2. `TIMESTAMP`

3. `TIMESTAMP WITH LOCAL TIME ZONE`

4. `TIMESTAMP WITH TIME ZONE`

For any pair of datatypes, Oracle converts the datatype that has a smaller number in the preceding list to the datatype with the larger number.

### Explicit Conversion of Datetime Datatypes

If you want to do explicit conversion of datetime datatypes, use the `CAST` SQL function. You can explicitly convert `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, and `TIMESTAMP WITH LOCAL TIME ZONE` to another datatype in the list.

See Also: Oracle Database SQL Reference

## Datetime SQL Functions

Datetime functions operate on date (`DATE`), timestamp (`TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, and `TIMESTAMP WITH LOCAL TIME ZONE`) and interval (`INTERVAL DAY TO SECOND`, `INTERVAL YEAR TO MONTH`) values.

Some of the datetime functions were designed for the Oracle `DATE` datatype. If you provide a timestamp value as their argument, then Oracle internally converts the input type to a `DATE` value. Oracle does not perform internal conversion for the `ROUND` and `TRUNC` functions.

Table 4-1 shows the datetime functions that were designed for the Oracle `DATE` datatype. It contains cross-references to more detailed descriptions of the functions.

Table 4-1 Datetime Functions Designed for the DATE Datatype

Function | Description
--- | ---
`ADD_MONTHS` | Retur ns the date `d` plus `n` months
`LAST_DAY` | Returns the last day of the month that contains `date`
`MONTHS_BETWEEN` | Returns the number of months between `date1` and `date2`
`NEW_TIME` | Returns the date and time in `zone2` time zone when the date and time in `zone1` time zone are `date` Note: This function takes as input only a limited number of time zones. You can have access to a much greater number of time zones by combining the `FROM_TZ` function and the datetime expression.
`NEXT_DAY` | Returns the date of the first weekday named by `char` that is later than `date`
`ROUND` (date) | Returns `date` rounded to the unit specified by the `fmt` format model
`TRUNC` (date) | Returns `date` with the time portion of the day truncated to the unit specified by the `fmt` format model

Table 4-2 describes additional datetime functions and contains cross-references to more detailed descriptions.

Table 4-2 Additional Datetime Functions

Datetime Function | Description
--- | ---
`CURRENT_DATE` | Returns the current date in the session time zone in a value in the Gregorian calendar, of the `DATE` datatype
`CURRENT_TIMESTAMP` | Returns the current date and time in the session time zone as a `TIMESTAMP WITH TIME ZONE` value
`DBTIMEZONE` | Returns the value of the database time zone. The value is a time zone offset or a time zone region name
`EXTRACT` (datetime) | Extracts and returns the value of a specified datetime field from a datetime or interval value expression
`FROM_TZ` | Converts a `TIMESTAMP` value at a time zone to a `TIMESTAMP WITH TIME ZONE` value
`LOCALTIMESTAMP` | Returns the current date and time in the session time zone in a value of the `TIMESTAMP` datatype
`NUMTODSINTERVAL` | Converts number `n` to an `INTERVAL DAY TO SECOND` literal
`NUMTOYMINTERVAL` | Converts number `n` to an `INTERVAL YEAR TO MONTH` literal
`SESSIONTIMEZONE` | Returns the value of the current session's time zone
`SYS_EXTRACT_UTC` | Extracts the UTC from a datetime with time zone offset
`SYSDATE` | Returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started
`SYSTIMESTAMP` | Returns the system date, including fractional seconds and time zone of the system on which the database resides
`TO_CHAR` (datetime) | Converts a datetime or interval value of `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, or `TIMESTAMP WITH LOCAL TIME ZONE` datatype to a value of `VARCHAR2` datatype in the format specified by the `fmt` date format
`TO_DSINTERVAL` | Converts a character string of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` datatype to a value of `INTERVAL DAY TO SECOND` datatype
`TO_NCHAR` (datetime) | Converts a datetime or interval value of `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, `TIMESTAMP WITH LOCAL TIME ZONE`, `INTERVAL MONTH TO YEAR`, or `INTERVAL DAY TO SECOND` datatype from the database character set to the national character set
`TO_TIMESTAMP` | Converts a character string of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` datatype to a value of `TIMESTAMP` datatype
`TO_TIMESTAMP_TZ` | Converts a character string of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` datatype to a value of the `TIMESTAMP WITH TIME ZONE` datatype
`TO_YMINTERVAL` | Converts a character string of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` datatype to a value of the `INTERVAL YEAR TO MONTH` datatype
`TZ_OFFSET` | Returns the time zone offset that corresponds to the entered value, based on the date that the statement is executed

See Also: Oracle Database SQL Reference

## Datetime and Time Zone Parameters and Environment Variables

This section includes the following topics:

- Datetime Format Parameters

- Time Zone Environment Variables

- Daylight Saving Time Session Parameter

### Datetime Format Parameters

Table 4-3 contains the names and descriptions of the datetime format parameters.

Table 4-3 Datetime Format Parameters

Parameter | Description
--- | ---
`NLS_DATE_FORMAT` | Defines the default date format to use with the `TO_CHAR` and `TO_DATE` functions
`NLS_TIMESTAMP_FORMAT` | Defines the default timestamp format to use with the `TO_CHAR` and `TO_TIMESTAMP` functions
`NLS_TIMESTAMP_TZ_FORMAT` | Defines the default timestamp with time zone format to use with the `TO_CHAR` and `TO_TIMESTAMP_TZ` functions

Their default values are derived from `NLS_TERRITORY`.

You can specify their values by setting them in the initialization parameter file. You can specify their values for a client as client environment variables.

You can also change their values by changing their value in the initialization parameter file and then restarting the instance.

To change their values during a session, use the `ALTER SESSION` statement.

See Also: "Date and Time Parameters" for more information, including examples "NLS_DATE_FORMAT" "NLS_TIMESTAMP_FORMAT" "NLS_TIMESTAMP_TZ_FORMAT"

### Time Zone Environment Variables

The time zone environment variables are:

- `ORA_TZFILE`, which specifies the Oracle time zone file used by the database

- `ORA_SDTZ`, which specifies the default session time zone

  See Also: "Choosing a Time Zone File" "Setting the Session Time Zone"

### Daylight Saving Time Session Parameter

`ERROR_ON_OVERLAP_TIME` is a session parameter that determines how Oracle handles an ambiguous datetime boundary value. Ambiguous datetime values can occur when the time changes between Daylight Saving Time and standard time.

The possible values are `TRUE` and `FALSE`. When `ERROR_ON_OVERLAP_TIME` is `TRUE`, then an error is returned when Oracle encounters an ambiguous datetime value. When `ERROR_ON_OVERLAP_TIME` is `FALSE`, then ambiguous datetime values are assumed to be the standard time representation for the region. The default value is `FALSE`.

See Also: "Support for Daylight Saving Time"

## Choosing a Time Zone File

The Oracle time zone files contain the valid time zone names. The following information is also included for each time zone:

- Offset from Coordinated Universal Time (UTC)

- Transition times for Daylight Saving Time

- Abbreviations for standard time and Daylight Saving Time

Two time zone files are included in the Oracle home directory. The default time zone file is `$ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat`, which contains all the time zones defined in the database. `$ORACLE_HOME/oracore/zoneinfo/timezone.dat` contains only the most commonly used time zones.

If you use the larger time zone file, then you must continue to use it unless you are sure that none of the additional time zones that it contains are used for data that is stored in the database. Also, all databases and client installations that share information must use the same time zone file.

To enable the use of `$ORACLE_HOME/oracore/zoneinfo/timezone.dat`, or if you are already using it as your time zone file and you want to continue to do so in an Oracle Database 10g environment, perform the following steps:

1. Shut down the database if it has been started.

2. Set the `ORA_TZFILE` environment variable to `$ORACLE_HOME/oracore/zoneinfo/timezone.dat`.

3. Restart the database.

Note: If you are already using the default time zone file, then it is not practical to change to the smaller time zone file because the database may contain data with time zones that are not part of the smaller time zone file.

Oracle's time zone data is derived from the public domain information available at `ftp://elsie.nci.nih.gov/pub/`. Oracle's time zone data may not reflect the most recent data available at this site.

You can obtain a list of time zone names and time zone abbreviations from the time zone file that is installed with your database by entering the following statement:

    SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES;

For the default time zone file, this statement results in output similar to the following:

    TZNAME               TZABBREV
    -------------------- ----------
    Africa/Algiers       LMT
    Africa/Algiers       PMT
    Africa/Algiers       WET
    Africa/Algiers       WEST
    Africa/Algiers       CET
    Africa/Algiers       CEST
    Africa/Cairo         LMT
    Africa/Cairo         EET
    Africa/Cairo         EEST
    Africa/Casablanca    LMT
    Africa/Casablanca    WET
    Africa/Casablanca    WEST
    Africa/Casablanca    CET
    ...
    W-SU                 LMT
    W-SU                 MMT
    W-SU                 MST
    W-SU                 MDST
    W-SU                 S
    W-SU                 MSD
    W-SU                 MSK
    W-SU                 EET
    W-SU                 EEST
    WET                  LMT
    WET                  WEST
    WET                  WET

    1393 rows selected.

There are 6 time zone abbreviations associated with the Africa/Algiers time zone, 3 abbreviations associated with the Africa/Cairo time zone, and 4 abbreviations associated with the Africa/Casablanca time zone. The following table shows the time zone abbreviations and their meanings.

Time Zone Abbreviation | Meaning
--- | ---
LMT | Local Mean Time
PMT | Paris Mean Time
WET | Western European Time
WEST | Western European Summer Time
CET | Central Europe Time
CEST | Central Europe Summer Time
EET | Eastern Europe Time
EEST | Eastern Europe Summer Time

Note that an abbreviation can be associated with more than one time zone. For example, CET is associated with both Africa/Algiers and Africa/Casablanca, as well as time zones in Europe.

If you want a list of time zones without repeating the time zone name for each abbreviation, use the following query:

    SELECT UNIQUE tzname FROM V$TIMEZONE_NAMES;

For the default time zone file, this results in output similar to the following:

    TZNAME
    --------------------
    Africa/Algiers
    Africa/Cairo
    Africa/Casablanca
    Africa/Ceuta
    ...
    US/Pacific
    US/Pacific-New
    US/Samoa
    UTC
    W-SU
    WET

The default time zone file contains more than 350 unique time zone names. The small time zone file contains more than 180 unique time zone names.

See Also: "Customizing Time Zone Data" "Time Zone Names" for a list of valid Oracle time zone names

## Upgrading the Time Zone File

The time zone files that are supplied with Oracle Database 10g have been updated from version 1 to version 2 to reflect changes in transition rules for some time zone regions. The changes may affect existing data of `TIMESTAMP WITH TIME ZONE` datatype. For example, when users enter `TIMESTAMP '2003-02-17 09:00 America/Sao_Paulo'`, Oracle converts the data to UTC based on the transition rules in the time zone file and stores the data on disk. When the version 1 transition rules were in effect, Oracle stored `'2003-02-17 11:00:00'` and the time zone ID for `'America/Sao_Paulo'` because the offset in this example was `'-02:00'`. The offset under version 2 transition rules is `'-03:00'`. When users retrieve the data, they receive `'2003-02-17 08:00:00 American/Sao_Paulo'`. There is a one-hour difference compared to the original data.

You can use the `$ORACLE_HOME/rdbms/admin/utltzuv2.sql` script to discover all columns of `TIMESTAMP WITH TIME ZONE` datatype in your database. Execute the script before you update the database time zone file to version 2. The result is stored in the `sys.sys_tzuv2_temptab` table. The table has 5 columns: `table_owner`, `table_name`, `column_name`, `rowcount`, `nested_tab`. The `nested_tab` column indicates whether the table mentioned in the `table_name` column is a nested table.

If your database has data that will be affected by the time zone file update, then back up the data before you upgrade the time zone file to version 2. After the upgrade, you must update the data to ensure that the data is stored based on the new rules. For example, if user `scott` has a table `tztab`, as in the following:

    CREATE TABLE tztab(x NUMBER PRIMARY KEY, y TIMESTAMP WITH TIME ZONE);
    INSERT INTO tztab VALUES(1, timestamp '2003-02-17 09:00:00 America/Sao_Paulo');

Before upgrading, you can create a table `tztab_back` (note that column `y` here is defined as `VARCHAR2` to preserve the original value):

    CREATE TABLE tztab_back(x NUMBER PRIMARY KEY, y VARCHAR2(256));
    INSERT INTO tztab_back
    SELECT x, TO_CHAR(y, 'YYYY-MM-DD HH24.MI.SSXFF TZR')
    FROM tztab;

After upgrading, you need to update the data in the table `tztab` using the value in `tztab_back`, as in the following:

    UPDATE tztab t SET t.y =
      (SELECT to_timestamp_tz(t1.y,'YYYY-MM-DD HH24.MI.SSXFF TZR')
    FROM tztab_back t1
    WHERE t.x=t1.x);

Or you can use the Export utility to export your data before the upgrade and then import your data again after the upgrade. See the comments in the `utltzuv2.sql` script for more information.

Although the transition rule changes may affect data of `TIMESTAMP WITH LOCAL TIME ZONE` datatype, there is no way to upgrade the data. The data cannot be upgraded because this type does not preserve the original time zone/region associated with the data.

Time zone regions in Brazil and Israel may have frequent transition rules changes, perhaps as often as every year. Use the time zone offset instead of the time zone region name to avoid storing inconsistent data.

Customers using time zone regions that have been updated in version 2 of the time zone files are required to update all Oracle9i Database clients and databases that will communicate with an Oracle Database 10g server. This ensures that all environments will have the same version of the time zone file, version 2. This is not a requirement for other customers, but Oracle still recommends that you do so. Users who need to update their time zone files to version 2 can find the following information on OracleMetaLink (`http://metalink.oracle.com`):

- `readme.txt` contains the list of time zone regions that have changed from version 1 to version 2.

- Actual time zone files for version 2 for the Oracle9i Database release.

- `utltzuv2.sql` script that must be run on the server side to find out if the database already has a column of type `TIMESTAMP` `WITH` `TIME` `ZONE`. It contains time zones that have changed from version 1 to version 2.

Oracle Database 10g clients that communicate with Oracle Database 10g servers automatically get version 2 of the time zone file, so there is no need to download the new time zone file.

See Also: `$ORACLE_HOME/oracore/zoneinfo/readme.txt` for detailed information about time zone file updates Oracle Database Upgrade Guide for upgrade information

## Setting the Database Time Zone

Set the database time zone when the database is created by using the `SET TIME_ZONE` clause of the `CREATE DATABASE` statement. If you do not set the database time zone, then it defaults to the time zone of the server's operating system.

The time zone may be set to an absolute offset from UTC or to a named region. For example, to set the time zone to an offset from UTC, use a statement similar to the following:

    CREATE DATABASE db01
    ...
    SET TIME_ZONE='-05:00';

The range of valid offsets is -12:00 to +14:00.

To set the time zone to a named region, use a statement similar to the following:

    CREATE DATABASE db01
    ...
    SET TIME_ZONE='Europe/London';

Note: The database time zone is relevant only for `TIMESTAMP WITH LOCAL TIME ZONE` columns. Oracle Corporation recommends that you set the database time zone to UTC (0:00) to avoid data conversion and improve performance when data is transferred among databases. This is especially important for distributed databases, replication, and exporting and importing.

You can change the database time zone by using the `SET TIME_ZONE` clause of the `ALTER DATABASE` statement. For example:

    ALTER DATABASE SET TIME_ZONE='05:00';
    ALTER DATABASE SET TIME_ZONE='Europe/London';

The `ALTER DATABASE SET TIME_ZONE` statement returns an error if the database contains a table with a `TIMESTAMP WITH LOCAL TIME ZONE` column and the column contains data.

The change does not take effect until the database has been shut down and restarted.

You can find out the database time zone by entering the following query:

    SELECT dbtimezone FROM DUAL;

## Setting the Session Time Zone

You can set the default session time zone with the `ORA_SDTZ` environment variable. When users retrieve `TIMESTAMP WITH LOCAL TIME ZONE` data, Oracle returns it in the users' session time zone. The session time zone also takes effect when a `TIMESTAMP` value is converted to the `TIMESTAMP WITH TIME ZONE` or `TIMESTAMP WITH LOCAL TIME ZONE` datatype.

Note: Setting the session time zone does not affect the value returned by the `SYSDATE` and `SYSTIMESTAMP` SQL function. `SYSDATE` returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started.

The `ORA_SDTZ` environment variable can be set to the following values:

- Operating system local time zone (`'OS_TZ'`)

- Database time zone (`'DB_TZ'`)

- Absolute offset from UTC (for example, `'-05:00'`)

- Time zone region name (for example, `'Europe/London'`)

To set `ORA_SDTZ`, use statements similar to one of the following in a UNIX environment (C shell):

    % setenv ORA_SDTZ 'OS_TZ'
    % setenv ORA_SDTZ 'DB_TZ'
    % setenv ORA_SDTZ '-05:00'
    % setenv ORA_SDTZ 'Europe/London'

You can change the time zone for a specific SQL session with the `SET TIME_ZONE` clause of the `ALTER SESSION` statement.

`TIME_ZONE` can be set to the following values:

- Default local time zone when the session was started (`local`)

- Database time zone (`dbtimezone`)

- Absolute offset from UTC (for example, `'+10:00'`)

- Time zone region name (for example, `'Asia/Hong_Kong'`)

Use `ALTER SESSION` statements similar to the following:

    ALTER SESSION SET TIME_ZONE=local;
    ALTER SESSION SET TIME_ZONE=dbtimezone;
    ALTER SESSION SET TIME_ZONE='+10:00';
    ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';

You can find out the current session time zone by entering the following query:

    SELECT sessiontimezone FROM DUAL;

## Converting Time Zones With the AT TIME ZONE Clause

A datetime SQL expression can be one of the following:

- A datetime column

- A compound expression that yields a datetime value

A datetime expression can include an `AT LOCAL` clause or an `AT TIME ZONE` clause. If you include an `AT LOCAL` clause, then the result is returned in the current session time zone. If you include the `AT TIME ZONE` clause, then use one of the following settings with the clause:

- Time zone offset: The string '`(+|-)HH:MM'` specifies a time zone as an offset from UTC. For example, `'-07:00'` specifies the time zone that is 7 hours behind UTC. For example, if the UTC time is 11:00 a.m., then the time in the `'-07:00'` time zone is 4:00 a.m.

- `DBTIMEZONE`: Oracle uses the database time zone established (explicitly or by default) during database creation.

- `SESSIONTIMEZONE`: Oracle uses the session time zone established by default or in the most recent `ALTER SESSION` statement.

- Time zone region name: Oracle returns the value in the time zone indicated by the time zone region name. For example, you can specify `Asia/Hong_Kong`.

- An expression: If an expression returns a character string with a valid time zone format, then Oracle returns the input in that time zone. Otherwise, Oracle returns an error.

The following example converts the datetime value in the `America/New_York` time zone to the datetime value in the `America/Los_Angeles` time zone.

Example 4-5 Converting a Datetime Value to Another Time Zone

    SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
         'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York')
         AT TIME ZONE 'America/Los_Angeles' "West Coast Time"
    FROM DUAL;

    West Coast Time
    ----------------------------------------------------------
    01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES

See Also: Oracle Database SQL Reference

## Support for Daylight Saving Time

Oracle automatically determines whether Daylight Saving Time is in effect for a specified time zone and returns the corresponding local time. The datetime value is usually sufficient for Oracle to determine whether Daylight Saving Time is in effect for a specified time zone. The periods when Daylight Saving Time begins or ends are boundary cases. For example, in the Eastern region of the United States, the time changes from 01:59:59 a.m. to 3:00:00 a.m. when Daylight Saving Time goes into effect. The interval between 02:00:00 and 02:59:59 a.m. does not exist. Values in that interval are invalid. When Daylight Saving Time ends, the time changes from 02:00:00 a.m. to 01:00:01 a.m. The interval between 01:00:01 and 02:00:00 a.m. is repeated. Values from that interval are ambiguous because they occur twice.

To resolve these boundary cases, Oracle uses the `TZR` and `TZD` format elements. `TZR` represents the time zone region in datetime input strings. Examples are '`Australia/North`', '`UTC`', and '`Singapore`'. `TZD` represents an abbreviated form of the time zone region with Daylight Saving Time information. Examples are '`PST`' for US/Pacific standard time and '`PDT`' for US/Pacific daylight time. To see a list of valid values for the `TZR` and `TZD` format elements, query the `TZNAME` and `TZABBREV` columns of the `V$TIMEZONE_NAMES` dynamic performance view.

See Also: "Time Zone Names" for a list of valid time zones

### Examples: The Effect of Daylight Saving Time on Datetime Calculations

The `TIMESTAMP` datatype does not accept time zone values and does not calculate Daylight Saving Time.

The `TIMESTAMP WITH TIME ZONE` and `TIMESTAMP WITH LOCAL TIME ZONE` datatypes have the following behavior:

- If a time zone region is associated with the datetime value, then the database server knows the Daylight Saving Time rules for the region and uses the rules in calculations.

- Daylight Saving Time is not calculated for regions that do not use Daylight Saving Time.

The rest of this section contains examples that use datetime datatypes. The examples use the `global_orders` table. It contains the `orderdate1` column of `TIMESTAMP` datatype and the `orderdate2` column of `TIMESTAMP WITH TIME ZONE` datatype. The `global_orders` table is created as follows:

    CREATE TABLE global_orders ( orderdate1 TIMESTAMP(0),
                          orderdate2 TIMESTAMP(0) WITH TIME ZONE);
    INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM',
                                '28-OCT-00 11:24:54 PM America/New_York');

Example 4-6 Comparing Daylight Saving Time Calculations Using TIMESTAMP WITH TIME ZONE and TIMESTAMP

    SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR
           FROM global_orders;

The following output results:

    ORDERDATE1+INTERVAL'8'HOUR      ORDERDATE2+INTERVAL'8'HOUR
    --------------------------      --------------------------
    29-OCT-00 07.24.54.000000 AM    29-OCT-00 06.24.54.000000 AM AMERICA/NEW_YORK

This example shows the effect of adding 8 hours to the columns. The time period includes a Daylight Saving Time boundary (a change from Daylight Saving Time to standard time). The `orderdate1` column is of `TIMESTAMP` datatype, which does not use Daylight Saving Time information and thus does not adjust for the change that took place in the 8-hour interval. The `TIMESTAMP WITH TIME ZONE` datatype does adjust for the change, so the `orderdate2` column shows the time as one hour earlier than the time shown in the `orderdate1` column.

Note: If you have created a `global_orders` table for the previous examples, then drop the `global_orders` table before you try Example 4-7 through Example 4-8.

Example 4-7 Comparing Daylight Saving Time Calculations Using TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP

The `TIMESTAMP WITH LOCAL TIME ZONE` datatype uses the value of `TIME_ZONE` that is set for the session environment. The following statements set the value of the `TIME_ZONE` session parameter and create a `global_orders` table. The `global_orders` table has one column of `TIMESTAMP` datatype and one column of `TIMESTAMP WITH LOCAL TIME ZONE` datatype.

    ALTER SESSION SET TIME_ZONE='America/New_York';
    CREATE TABLE global_orders ( orderdate1 TIMESTAMP(0),
                                 orderdate2 TIMESTAMP(0) WITH LOCAL TIME ZONE );
    INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM',
                                       '28-OCT-00 11:24:54 PM' );

Add 8 hours to both columns.

    SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR
    FROM global_orders;

Because a time zone region is associated with the datetime value for `orderdate2`, the Oracle server uses the Daylight Saving Time rules for the region. Thus the output is the same as in Example 4-6. There is a one-hour difference between the two calculations because Daylight Saving Time is not calculated for the `TIMESTAMP` datatype, and the calculation crosses a Daylight Saving Time boundary.

Example 4-8 Daylight Saving Time Is Not Calculated for Regions That Do Not Use Daylight Saving Time

Set the time zone region to UTC. UTC does not use Daylight Saving Time.

    ALTER SESSION SET TIME_ZONE='UTC';

Truncate the `global_orders` table.

    TRUNCATE TABLE global_orders;

Insert values into the `global_orders` table.

    INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM',
                                     TIMESTAMP '2000-10-28 23:24:54 ' );

Add 8 hours to the columns.

    SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR
    FROM global_orders;

The following output results.

    ORDERDATE1+INTERVAL'8'HOUR                 ORDERDATE2+INTERVAL'8'HOUR
    --------------------------                 ---------------------------
    29-OCT-00 07.24.54.000000000 AM            29-OCT-00 07.24.54.000000000 AM UTC

The times are the same because Daylight Saving Time is not calculated for the UTC time zone region.